import pymysql

host = "127.0.0.1"
user = "root"
password = "han"
port = 3307
database = "school"


class DBUtils:
    # 添加类属性
    conn = None

    # 获取链接
    @classmethod
    def __getConn(cls):
        if cls.conn is None:
            # cls.conn = pymysql.connect(host=host, user=user, password=password, port=port, database=database)
                cls.conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",password="iHRM_student_2021", database="ihrm",charset="utf8")
        return cls.conn

    # 关闭链接
    @classmethod
    def __closeConn(cls):
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    # 查询一条
    @classmethod
    def select_one(cls, sql):
        cursor = None
        result = None
        try:
            cls.conn = cls.__getConn()
            cursor = cls.conn.cursor()  # 获取游标
            cursor.execute(sql)  # 执行sql
            result = cursor.fetchone()  # 提取结果集

        except Exception as e:
            print(e)
        finally:
            cursor.close()  # 关闭游标
            cls.__closeConn()  # 关闭连接
            # print("最后执行")
            return result

    # 查询多条
    @classmethod
    def select_all(cls, sql):
        cursor = None
        result = None
        try:
            cls.conn = cls.__getConn()
            cursor = cls.conn.cursor()  # 获取游标
            cursor.rownumber = 0  # 将游标位置回退为0
            cursor.execute(sql)  # 执行sql
            result = cursor.fetchall()  # 提取结果集

        except Exception as e:
            print(e)
        finally:
            cursor.close()  # 关闭游标
            cls.__closeConn()  # 关闭连接
            return result

    # 增删改
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            cls.conn = cls.__getConn()
            cursor = cls.conn.cursor()
            cursor.execute(sql)
            cls.conn.commit()  # 提交事务
            print(f"受影响的行数：{cls.conn.affected_rows()}")
        except Exception as e:
            print(e)
            cls.conn.rollback()  # 回滚事务
        finally:
            cursor.close()  # 关闭游标
            cls.__closeConn()  # 关闭连接


if __name__ == '__main__':
    res = DBUtils.select_one("select * from bs_user")
    # res_all = DBUtils.select_all("select * from user")
    # DBUtils.uid_db("update user set username='大大怪1' where id =12 ")
    # print(res_all)
    print(res)
